﻿Imports System.Data
Imports System.Data.SqlClient
Public Class DALSolicitante
    Dim cmd As SqlCommand
    Function Inserir(ByVal nome As String, ByVal empresa As String, ByVal telefone As Integer, ByVal celular As Integer, ByVal cidade As String, ByVal email As String, ByVal obs As String) As Boolean
        Dim ret As Boolean
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute spCliente @nome,@empresa,@telefone,@celular,@cidade,@email,@obs,@codigo;"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = nome
            cmd.Parameters.Add(New SqlParameter("@empresa", SqlDbType.VarChar)).Value = empresa
            cmd.Parameters.Add(New SqlParameter("@telefone", SqlDbType.Int)).Value = telefone
            cmd.Parameters.Add(New SqlParameter("@celular", SqlDbType.Int)).Value = celular
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = cidade
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = email
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = obs
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = 0
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        If ret = True Then
            Return True
        Else
            Return False
        End If
    End Function
    Function ValidaDuplicata(ByVal Solicitante As String, ByVal codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Age_PessoaExterna where ext_nome = @solicitante and sta_codigo=1 and ext_codigo <> @codigo;"
            cmd.Parameters.Add(New SqlParameter("@solicitante", SqlDbType.VarChar)).Value = Solicitante
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function


    Function Alterar(ByVal nome As String, ByVal empresa As String, ByVal telefone As Integer, ByVal celular As Integer, ByVal codigo As Integer, ByVal cidade As String, ByVal email As String, ByVal obs As String) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute spCliente @nome,@empresa,@telefone,@celular,@cidade,@email,@obs,@codigo;"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = nome
            cmd.Parameters.Add(New SqlParameter("@empresa", SqlDbType.VarChar)).Value = empresa
            cmd.Parameters.Add(New SqlParameter("@telefone", SqlDbType.VarChar)).Value = telefone
            cmd.Parameters.Add(New SqlParameter("@celular", SqlDbType.VarChar)).Value = celular
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = cidade
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = email
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = obs
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
    Function ValidaExclusao(ByVal Codigo As Integer, ByVal Tipoagendador As String) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_Agendamento where agd_codigo = @codigo and tpa_codigo=@codtipo;"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@codtipo", SqlDbType.VarChar)).Value = Tipoagendador
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function Excluir(ByVal Codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "delete from Age_PessoaExterna where ext_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
    Function ExcluirUpdate(ByVal Codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "update Age_PessoaExterna set" & _
                " sta_codigo=2 where ext_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function

    'Function localizar(ByVal codigo As Integer, ByVal descricao As String) As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select * from  vw_LocalizaAgendadores where " & _
    '            " agd_codigo = @codigo and agd_desc=@desc"
    '        cmd.Parameters.Add(New SqlParameter("@desc", SqlDbType.VarChar)).Value = descricao
    '        cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    'Function localizar(ByVal nome As String) As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select * from  vw_LocalizaAgendadores where " & _
    '            " ext_nome like @nome and sta_codigo=1 order by ext_nome, ext_empresa"
    '        cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & nome & "%"
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from  Age_PessoaExterna where " & _
                " ext_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function Localizar(TP As Integer, ByVal Nome As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case TP
                Case 1
                    cmd.CommandText = "select * from  vwPessoaExterna where sta_descricao='Ativo' order by ext_nome, ext_empresa"
                Case 2
                    cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & Nome & "%"

            End Select


            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    'Function localizarTudo(ByVal codigo As Integer) As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select *from  age_pessoaexterna where " & _
    '            " ext_codigo like @codigo"
    '        cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
End Class
